WITH
  UNIONFUENTES AS (octubre_2024)
  SELECT
    CURRENT_DATE() AS Fecha_de_Ingreso,
    NULL AS CASO,
    'EN ESTUDIO' AS Posible_contacto,
    SAFE_CAST(Fecha_Consulta AS DATE) AS Fecha_Notificacion,
    'BOGOTA DC' AS Departamento_Notificacion,
    'BOGOTA D.C.' AS Ciudad_Municipio_Notificacion,
    SAFE_CAST(Edad AS INTEGER) AS Edad,
    Tipo_Edad AS Uni_Med,
    Genero AS Sexo,
    'En Estudio' AS fuente_o_tipo_de_contagio,
    CASE
    WHEN Clasificacion IN ('Ambulatorio','NA','SIN DATO') THEN 'Casa' WHEN Clasificacion ='Hospitalizado  1. Hosp General' THEN 'Hospital' WHEN Clasificacion ='Hospitalizado  3. UCI' THEN 'Hospital UCI' WHEN Clasificacion IN ('Condicion Final 2.  Muerto', 'Condicion Final 2. Muerto') THEN 'Fallecido'
    END AS Ubicacion,
    CASE
    WHEN Clasificacion IN ('Ambulatorio','NA','SIN DATO') THEN 'Leve' WHEN Clasificacion ='Hospitalizado  1. Hosp General' THEN 'Moderado' WHEN Clasificacion ='Hospitalizado  3. UCI' THEN 'Grave' WHEN Clasificacion IN ('Condicion Final 2.  Muerto', 'Condicion Final 2. Muerto') THEN ''
    END AS Estado,
    Primer_Nombre AS Nombre1,
    Segundo_Nombre AS Nombre2,
    Primer_Apellido AS Apellido1,
    Segundo_Apellido AS Apellido2,
    Tipo_Identificacion AS Tipo_Identificacion,
    Identificacion AS Documentodeidentificacion_Correcto,
    NULL AS FI_Pais,
    Fecha_Inicio_Sintomas AS Fecha_de_Inicio_de_Sintomas,
    SAFE_CAST(Fecha_Consulta AS DATE) AS Fecha_de_Consulta,
    SAFE_CAST(Fecha_analisis AS DATE) AS Fecha_de_Diagnostico,
    Aseguradora AS EAPB,
    Institucion_Procedencia AS IPS_Notificacion,
    Direccion AS Direccion_de_paciente,
    Localidad AS Localidad,
    '' AS Lugar_de_Reporte_Bogota,
    Radicado AS Radicado_1,
    SAFE_CAST(Fecha_Consulta AS DATE) AS FECHA_DE_TOMA_DE_MUESTRA,
    Telefono AS Telefono_Celular,
    Identificacion AS CEDULA_INS,
    CONCAT(Primer_Nombre,IF(Segundo_Nombre= 'NA', '',Segundo_Nombre),Primer_Apellido,IF(Segundo_Apellido= 'NA', '',Segundo_Apellido) ) AS Nombre_Concatenado,
    Tipo_muestra AS TIPO_DE_PRUEBA,
    Resultado_NUEVO_CORONAVIRUS_SARS_CoV_2 AS Resultado,
    DATE(Fecha_Resultado_NUEVO_CORONAVIRUS_SARS_CoV_2) AS Fecha_Resultado
  FROM
    `proyecto-covid-sds.SECRETARIA_SALUD.silaps`
  WHERE
    Resultado_NUEVO_CORONAVIRUS_SARS_CoV_2 LIKE '%Positivo%'
--     AND EXTRACT(YEAR 
--     FROM
--       DATE(Fecha_Resultado_NUEVO_CORONAVIRUS_SARS_CoV_2)) >= 2021 
      and Fecha_Resultado_NUEVO_CORONAVIRUS_SARS_CoV_2 >= '2021-12-31'
    ##################SISMUESTRA###################
  UNION ALL
  SELECT
    CURRENT_DATE() AS Fecha_de_Ingreso,
    NULL AS CASO,
    'EN ESTUDIO' AS Posible_contacto,
    FechaMuestra AS Fecha_Notificacion,
    Departamento AS Departamento_Notificacion,
    IF(Municipio='BOGOTA','BOGOTA D.C.',Municipio) AS Ciudad_Municipio_Notificacion,
    Edad AS Edad,
    NULL AS Uni_Med,
    Sexo AS Sexo,
    'En Estudio' AS fuente_o_tipo_de_contagio,
    'Casa' AS Ubicacion,
    'Leve' AS Estado,
    CASE WHEN PrimerNombre LIKE '% %' AND SegundoNombre = '' THEN SPLIT(PrimerNombre, ' ')[OFFSET(0)] ELSE  PrimerNombre END   AS Nombre1,
    CASE WHEN SegundoNombre = '' AND PrimerNombre LIKE '% %'  THEN SPLIT(PrimerNombre, ' ')[OFFSET(1)] ELSE SegundoNombre END   AS Nombre2,
    CASE WHEN PrimerApellido LIKE '% %' AND SegundoApellido = '' THEN SPLIT(PrimerApellido, ' ')[OFFSET(0)] ELSE  PrimerApellido END  AS Apellido1,
    CASE WHEN SegundoApellido = '' AND PrimerApellido LIKE '% %'  THEN SPLIT(PrimerApellido, ' ')[OFFSET(1)] ELSE SegundoApellido END  AS Apellido2,
    TipoDocumento AS Tipo_Identificacion,
    Documento AS Documentodeidentificacion_Correcto,
    NULL AS FI_Pais,
    NULL AS Fecha_de_Inicio_de_Sintomas,
    FechaMuestra AS Fecha_de_Consulta,
    Fecha_Resultado AS Fecha_de_Diagnostico,
    EAPB AS EAPB,
    IPS_Envia AS IPS_Notificacion,
    Direccion AS Direccion_de_paciente,
    NULL AS Localidad,
    LaboratorioNotifica AS Lugar_de_Reporte_Bogota,
    '' AS Radicado_1,
    FechaMuestra AS FECHA_DE_TOMA_DE_MUESTRA,
    Telefono AS Telefono_Celular,
    Documento AS CEDULA_INS,
    CONCAT(PrimerNombre,IF(SegundoNombrE='NA' ,'',SegundoNombre ),PrimerApellido,IF(SegundoApellido='NA' ,'',SegundoApellido ) ) AS Nombre_Concatenado,
    Tipo_Prueba AS TIPO_DE_PRUEBA,
    Resultado AS Resultado,
    Fecha_Resultado AS Fecha_Resultado
  FROM
    `proyecto-covid-sds.SECRETARIA_SALUD.sismuestras`
  WHERE
    UPPER(departamento_residencia) in ('BOGOTA', 'BOGOTA D.C','BOGOTA DC',NULL,'') and
    Resultado LIKE '%POSITIVO%'  AND Municipio= 'BOGOTA D.C.'
--     AND EXTRACT(YEAR
--     FROM
--       Fecha_Resultado) >= 2021 
      and Fecha_Resultado >= '2021-12-31'
    ##################ANTIGENOS###################
  UNION ALL
  SELECT
    CURRENT_DATE() AS Fecha_de_Ingreso,
    NULL AS CASO,
    'EN ESTUDIO' AS Posible_contacto,
     SAFE_CAST(FEHCA AS DATE) AS Fecha_Notificacion,
    'BOGOTA DC' AS Departamento_Notificacion,
    IF(MUNICIPIO='BOGOTA','BOGOTA D.C.',MUNICIPIO) AS Ciudad_Municipio_Notificacion,
    SAFE_CAST(Edad AS INT64) AS Edad,
    NULL AS Uni_Med,
    genero AS Sexo,
    'En Estudio' AS fuente_o_tipo_de_contagio,
    'Casa' AS Ubicacion,
    'Leve' AS Estado,
    `proyecto-covid-sds.udfs.UNCONCAT_NOMBRE1`(NOMBRE_CCOMPLETO) Nombre1,
    `proyecto-covid-sds.udfs.UNCONCAT_NOMBRE2`(NOMBRE_CCOMPLETO) AS Nombre2,
    `proyecto-covid-sds.udfs.UNCONCAT_APELLIDO1`(NOMBRE_CCOMPLETO) AS Apellido1,
    `proyecto-covid-sds.udfs.UNCONCAT_APELLIDO2`(NOMBRE_CCOMPLETO) AS Apellido2,
    Tipo_Documento AS Tipo_Identificacion,
    IDENTIFICACION AS Documentodeidentificacion_Correcto,
    NULL AS FI_Pais,
    NULL AS Fecha_de_Inicio_de_Sintomas,
    NULL AS Fecha_de_Consulta,
    NULL AS Fecha_de_Diagnostico,
    EPS AS EAPB,
    IPS AS IPS_Notificacion,
    NULL AS Direccion_de_paciente,
    NULL AS Localidad,
    IPS AS Lugar_de_Reporte_Bogota,
    '' AS Radicado_1,
    NULL AS FECHA_DE_TOMA_DE_MUESTRA,
    NULL AS Telefono_Celular,
    Identificacion AS CEDULA_INS,
    REPLACE(NOMBRE_CCOMPLETO," ","") AS Nombre_Concatenado,
    tipo_prueba AS TIPO_DE_PRUEBA,
    Resultado AS Resultado,
     SAFE_CAST(FEHCA AS DATE) AS Fecha_Resultado
  FROM
    `proyecto-covid-sds.SECRETARIA_SALUD.antigenos`
  WHERE
    Resultado LIKE '%POSITIVO%'
--     AND EXTRACT(YEAR
--     FROM
--       SAFE_CAST(FEHCA AS DATE) ) >= 2021 
      and fehca  >= '2021-12-31')
      
      select * from unionfuentes up
      LEFT JOIN `proyecto-covid-sds.SECRETARIA_SALUD.positivos` AS SP ON UP. Documentodeidentificacion_Correcto = SP.CEDULA__INS

      LEFT JOIN (SELECT * EXCEPT (RN)
            FROM (
                  SELECT *, ROW_NUMBER() OVER (PARTITION BY num_ide_,fec_not,fec_con_  ORDER BY fec_con_ DESC ) AS RN
                  FROM `proyecto-covid-sds.salud_publica.siivigila` WHERE UPPER(nmun_resi) LIKE '%BOG%' AND EXTRACT(YEAR FROM fec_not) >= 2021 
                  )
            WHERE RN=1 ) AS SIV 
  ON UP. Documentodeidentificacion_Correcto = SIV.num_ide_
  LEFT JOIN (
            SELECT NUM_IDE AS num_ide  FROM `proyecto-covid-sds.salud_publica.duplicados_ins` 
            UNION ALL
            SELECT NUMIDE FROM `proyecto-covid-sds.salud_publica.bogota_fr`  
          ) on num_ide = Documentodeidentificacion_Correcto 
          
      where Documentodeidentificacion_Correcto='87354547'